﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace TypingG
{
    class SQLServer
    {
        private static SqlConnection con;
        private static SqlDataAdapter sd = new SqlDataAdapter();
        private static BindingSource bs = new BindingSource();
        public static void Start(string DataSource,string DatabaseName)
        {
            string sour = "Data Source=" + DataSource + ";" + "Initial Catalog=" + DatabaseName + ";" + "Integrated Security=TRUE";
            con = new SqlConnection(sour);
           
        }
        public static void addAcc(string hoten,string cmnd,string namsinh)
        {
            sd.InsertCommand = new SqlCommand("INSERT INTO KhachHang VALUES (@FullName,@CMND,@NamSinh)", con);
            sd.InsertCommand.Parameters.Add("@FullName", System.Data.SqlDbType.NVarChar).Value = hoten;
            sd.InsertCommand.Parameters.Add("@CMND", System.Data.SqlDbType.VarChar).Value = cmnd;
            sd.InsertCommand.Parameters.Add("@NamSinh", System.Data.SqlDbType.VarChar).Value = namsinh;
            con.Open();
            sd.InsertCommand.ExecuteNonQuery();
            
            con.Close();

        }
        public static void addID(string ID, string CMND)
        {
            sd.InsertCommand = new SqlCommand("INSERT INTO TaiKhoan VALUES (@ID,@CMND)", con);
            sd.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.NVarChar).Value = ID;
            sd.InsertCommand.Parameters.Add("@CMND", System.Data.SqlDbType.VarChar).Value = CMND;
            con.Open();
            sd.InsertCommand.ExecuteNonQuery();
            con.Close();

        }
        public static void addIDLevel(string ID, string Pass, string Level,string _exp)
        {
            sd.InsertCommand = new SqlCommand("INSERT INTO CapDoTaiKhoan VALUES (@ID,@Pass,@Capdo,@exps)", con);
            sd.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.NVarChar).Value = ID;
            sd.InsertCommand.Parameters.Add("@Pass", System.Data.SqlDbType.VarChar).Value = Pass;
            sd.InsertCommand.Parameters.Add("@Capdo", System.Data.SqlDbType.NVarChar).Value = Level;
            sd.InsertCommand.Parameters.Add("@exps", System.Data.SqlDbType.VarChar).Value = _exp;
            
            con.Open();
            sd.InsertCommand.ExecuteNonQuery();
            con.Close();
        }
        public static DataSet ShowData(System.Windows.Forms.DataGridView dg,string table)
        {
            DataSet ds = new DataSet();
            
            string synTable = "SELECT * FROM " + table;
            sd.SelectCommand = new SqlCommand(synTable,con);
            ds.Clear();
            sd.Fill(ds);
            dg.DataSource = ds.Tables[0];
            return ds;
        }
        public static void deleteMember(string tableName,string member,string deleteName)
        {
            
            sd.DeleteCommand = new SqlCommand(("DELETE FROM " + tableName + " WHERE " + member + "=@"+member),con);
            
            sd.DeleteCommand.Parameters.Add(("@" + member), SqlDbType.VarChar).Value = deleteName;
          
            con.Open();
            sd.DeleteCommand.ExecuteNonQuery();
            con.Close();
        }
        //return -1: not exists account else return position of rows
        public static int CheckLevelAccount(string acc, string pass)
        {
            //get dataset in table CapDoTaiKhoan
            DataSet ds=ShowData(new DataGridView(), "CapDoTaiKhoan");
            bool check = false;
            int row = 0;
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                if (ds.Tables[0].Rows[i][0].ToString().ToLower() == acc.ToLower() && ds.Tables[0].Rows[i][1].ToString() == pass)
                {
                    check = true;
                    row = i;
                }
            }
            if (check == false) return -1;
            else return row;
        }
        public static void updateID(string _acc,string _level,string _exp)
        {
            sd.UpdateCommand = new SqlCommand("UPDATE CapDoTaiKhoan SET Exps=@Exps,Capdo=@Capdo WHERE ID=@ID", con);
            sd.UpdateCommand.Parameters.Add("@ID", SqlDbType.NVarChar).Value = _acc;
            sd.UpdateCommand.Parameters.Add("@Exps", SqlDbType.VarChar).Value = _exp;
            sd.UpdateCommand.Parameters.Add("@Capdo", SqlDbType.NVarChar).Value = _level;

            con.Open();
            sd.UpdateCommand.ExecuteNonQuery();
            con.Close();
        }

    }
}
